package com.tjetc.dao;

import com.tjetc.domain.Coach;
import com.tjetc.domain.Player;
import com.tjetc.domain.*;
import com.tjetc.utils.Db;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class CoachDao {
    private QueryRunner runner=new QueryRunner();
    public Coach log(String username, String password) throws SQLException {
        return Db.runner.query("select * from coach where username=? and password=?",new BeanHandler<>(Coach.class),username,password);
    }

    public List<Coach> showCoachSalary() throws SQLException {
        return Db.runner.query("select name,salary from coach",new BeanListHandler<>(Coach.class));
    }

    public List<Coach> showCoach() throws SQLException {
        return Db.runner.query("select * from coach ",new BeanListHandler<>(Coach.class));
    }

    public Coach findByCoachName(String coachName) throws SQLException {
        return Db.runner.query("select * from coach where  name=?",new BeanHandler<>(Coach.class),coachName);
    }


    public void update(int coachId, String coachUserName, String coachPassWord, String coachName1, int coachAge, int coachExperience, int coachTeamId, int coachSalary, String coachName) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        runner.update(Db.getConnection(),"update coach set id=?,username=?,password=?,name =?,age =?,experience=?,team_id=?,salary=? where name =?",coachId,coachUserName,coachPassWord,coachName1,coachAge,coachExperience,coachTeamId,coachSalary,coachName);
    }

    public void dismiss(String coachName) throws SQLException {
        runner.update(Db.getConnection(),"update coach set team_id=? where name =?",0,coachName);
    }

    public Coach findByname(String name) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from coach where name=?",new BeanHandler<>(Coach.class,processor),name);
    }

    public Team findTeamByTeamId(int teamId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from team where id=?",new BeanHandler<>(Team.class,processor),teamId);
    }

    public boolean delCoachByname(String name) throws SQLException {
        int i = Db.runner.update(Db.getConnection(), "delete from coach where name =?", name);
        return i!=0;
    }

    public boolean updateUsername(String newUsername, String name) throws SQLException {
        int i = Db.runner.update(Db.getConnection(), "update coach set username=? where name=?", newUsername, name);
        return i!=0;
    }

    public boolean updatePassword(String newPassword, String name) throws SQLException {
        int i = Db.runner.update(Db.getConnection(), "update coach set password=? where name=?", newPassword, name);
        return i!=0;
    }

    public boolean updateTeamId(String newTeamId, String name) throws SQLException {
        int i = Db.runner.update(Db.getConnection(), "update coach set team_id=? where name=?", newTeamId, name);
        return i!=0;
    }

    public boolean updateSalary(String newSalary, String name) throws SQLException {
        int i = Db.runner.update(Db.getConnection(), "update coach set salary=? where name=?", newSalary, name);
        return i!=0;
    }

    public Coach findByUsername(String username) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from coach where username=?",new BeanHandler<>(Coach.class,processor),username);
    }


    public void addPlayer(Player player) throws SQLException {
        runner.update(Db.getConnection(),"insert into player (name,age,height,weight,position,team_id,salary) values (?,?,?,?,?,?,?)",player.getName(),player.getAge(),player.getHeight(),player.getWeight(),player.getPosition(),player.getTeamId(),player.getSalary());
    }

    public Player findPlayerByName(String name) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from player where name=?",new BeanHandler<>(Player.class,processor),name);
    }

    public boolean delPlayerByName(String name) throws SQLException {
        int i = Db.runner.update(Db.getConnection(), "delete from player where name =?", name);
        return i!=0;
    }

    public void showPlayer() {
    }

    public List<Player> getPlayersByTeamId(int teamId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from player where team_id=?",new BeanListHandler<>(Player.class,processor),teamId);
    }

    public List<Player> getAllPlayer() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from player",new BeanListHandler<>(Player.class,processor));

    }

    public List<Game> findGameByHomeTeamId(String homeTeamName) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from game,team where team.name=?",new BeanListHandler<>(Game.class),homeTeamName);
    }

    public Game findGameById(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from game where id=?",new BeanHandler<>(Game.class,processor),id);

    }

    public List<Game> findGameByTime(Date date) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from game where time=?",new BeanListHandler<>(Game.class,processor),date);
    }

    public boolean updataGameTime(Date date, int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        int i = Db.runner.update("update game set time=? where id=?", date, id);
        return i!=0;
    }

    public boolean delGame(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        int i = Db.runner.update("delete from game where id=?", id);
        return i!=0;
    }

    public List<Game> getMyGame(int teamId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from game where home_team_id=? or away_team_id=?",new BeanListHandler<>(Game.class,processor),teamId,teamId);
    }

    public List<Game> getAllGame() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from game",new BeanListHandler<>(Game.class,processor));
    }

    public List<TeamScore> Teamanalysis() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query(Db.getConnection(),"select * from team_score",new BeanListHandler<>(TeamScore.class,processor));
    }

    public List<PlayerScore> Playeranalysis() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query(Db.getConnection(),"select * from player_score",new BeanListHandler<>(PlayerScore.class,processor));
    }

    public Player findPlayerById(int playerId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query(Db.getConnection(),"select * from player where id=?",new BeanHandler<>(Player.class,processor),playerId);
    }

    public Detail findDetailById(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query(Db.getConnection(),"select * from detail where id=?",new BeanHandler<>(Detail.class,processor),id);
    }

    public boolean addTraining(int teamId, int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        int update = Db.runner.update("insert into training (detail_id,team_id) values (?,?)", id, teamId);
        return update!=0;
    }

    public Detail getDetailBytime(int id, int time) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query(Db.getConnection(),"select * from detail,training where time =? and detail.id=detail_id=?;",new BeanHandler<>(Detail.class,processor),time,id);
    }

    public List<Detail> showTraining(int teamId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query(Db.getConnection(),"select * from training,detail where team_id=? and detail_id=detail.id ",new BeanListHandler<>(Detail.class,processor),teamId);
    }

    public boolean updataTraining(int trainingId, int detailId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        int i = Db.runner.update("update training set detail_id =? where id=?", detailId, trainingId);
        return i!=0;
    }

    public boolean delTraining(int id) throws SQLException {
        int update = Db.runner.update("delete from training where id=?",id);
        return update!=0;
    }

    public boolean addDetail(String s, int time) throws SQLException {
        int i = Db.runner.update("insert into detail (item,time) values (?,?)", s, time);
        return i!=0;
    }

    public Detail findDetail(String s, int time) throws SQLException {
        return Db.runner.query(Db.getConnection(),"select * from detail where item=? and time=?",new BeanHandler<>(Detail.class),s,time);
    }

    public boolean updateDeatail(String s, int time, int id) throws SQLException {
        int update = Db.runner.update("update detail set item=?,time=? where id=?",s, time, id);
        return update!=0;
    }

    public List<Detail> findDetailByname(String name) throws SQLException {
        return Db.runner.query("select * from detail where item=?",new BeanListHandler<>(Detail.class),name);
    }

    public boolean delDetail(String name, int time) throws SQLException {
        int update = Db.runner.update("delete from detail where item=? and time=?",name,time);
        return update!=0;
    }

    public List<Detail> showDetail() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from detail ",new BeanListHandler<>(Detail.class,processor));
    }

    public Equip findEquipByName(String name) throws SQLException {
        return Db.runner.query(Db.getConnection(),"select * from equip where name=?",new BeanHandler<>(Equip.class),name);
    }

    public boolean addEquip(String name, double price, int num) throws SQLException {
        int i = Db.runner.update("insert into equip (name,price,num) values (?,?,?)", name, price,num);
        return i!=0;
    }

    public boolean updataequip(String name, double price, int num) throws SQLException {
        int update = Db.runner.update("update equip set price=?,num=? where name=?",price,num,name);
        return update!=0;
    }

    public boolean delEquip(String name) throws SQLException {
        int update = Db.runner.update("delete from equip where name =?",name);
        return update!=0;
    }

    public List<Equip> getEquips() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return Db.runner.query("select * from equip ",new BeanListHandler<>(Equip.class,processor));

    }

    public boolean addGame(int home, int away, Date date, String loca) throws SQLException {
        int update = Db.runner.update("insert into game (time,location,home_team_id,away_team_id) values (?,?,?,?)",date,loca,home,away);
        return update!=0;
    }
}
